package cn.dida.dao;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import cn.dida.model.LgsNode;
import cn.dida.model.Pageable;
import org.springframework.transaction.annotation.Transactional;

/**
 * <p>节点管理 数据交互方法</p>
 * @auther lisha
 * @date 2017-2-8 12:01:15
 */
@Repository
public class LgsNodeDao extends DaoBase {
	
	/**
	 * 新增节点
	 * @param lgsNode 节点实体类
	 * @return 返回受影响的记录行数
	 * @date 2017-2-8 12:04
	 */
	@Transactional
	public int insertLgsNode (LgsNode lgsNode) {
		 String sql = "insert into lgs_node(name, c_time, c_admin, sta,company_id, type) values (:name, :cTime, "
		 		+ ":cAdmin, :sta, :companyId, :type)";

//添加节点
		lgsNode.setcTime(new Date().getTime());
		KeyHolder keyHolder=new GeneratedKeyHolder();
		SqlParameterSource ps=new BeanPropertySqlParameterSource(lgsNode);
		namedParameterJdbcTemplate.update(sql, ps, keyHolder);
		Integer nodeId = keyHolder.getKey().intValue();


		//添加关联关系  ERP适用单据 与 节点
		String biz = lgsNode.getBiztypes();
		String[] biztypes = null;
		if(StringUtils.isNotBlank(biz)){
			biztypes = lgsNode.getBiztypes().split(",");
		}
		if( biztypes != null && biztypes.length>0){
			Integer companyId = lgsNode.getCompanyId();
			for(int i=0;i<biztypes.length;i++){
				Integer biztypeId= Integer.parseInt(biztypes[i]);
				deleteRes(companyId,biztypeId);
				insertRes(companyId,nodeId,biztypeId);
			}

		}
		return nodeId;
	}

	/** 修改节点（仅允许修改名称和状态）
	 * @param companyId 公司id
	 * @param lgsNode 节点实体类
	 * @return 返回受影响的记录行数
	 * @date 2017-2-8 12:07
	 */
	@Transactional
	public int updateLgsNode (int companyId, LgsNode lgsNode) {
		String sql = "update lgs_node set name=:name, sta=:sta where ID = :id and company_id =:companyId ";
        HashMap<String, Object> paramMap = new HashMap<>();
        Integer nodeId = lgsNode.getId();

        //修改关联关系(删除关联， 添加关联)
		String biz = lgsNode.getBiztypes();
		String[] biztypes = null;
		if(StringUtils.isNotBlank(biz)){
		 biztypes = lgsNode.getBiztypes().split(",");
		}
		String del = "delete from res_node_biztype where node_id = ?";
		jdbcTemplate.update(del,nodeId);
		if( biztypes != null && biztypes.length>0){
			for(int i=0;i<biztypes.length;i++){
				Integer biztypeId= Integer.parseInt(biztypes[i]);
				insertRes(companyId,nodeId,biztypeId);
			}
		}
		//修改节点
		paramMap.put("name", lgsNode.getName());
		paramMap.put("sta", lgsNode.getSta());
		paramMap.put("id", nodeId);
		paramMap.put("companyId", companyId);
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}
	
	/**
	 * 节点管理列表
	 * @param name 节点名称
	 * @return 节点列表
	 * @date 2017-2-8 13:56
	 */
	public Pageable<LgsNode> query (String name, Integer type, Integer sta, int companyId, int page) {
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from lgs_node where company_id = ?");
        StringBuffer sqlQueryResult = new StringBuffer("select * from lgs_node where company_id = ?");

        args = ArrayUtils.add(args, companyId);
        StringBuffer sqlWhere = new StringBuffer();
        if(StringUtils.isNotBlank(name)){
        	sqlWhere.append(" and name like ?");
            args = ArrayUtils.add(args, "%" + name + "%");
        }
        if(type != null) {
        	sqlWhere.append(" and type = ?");
            args = ArrayUtils.add(args, type);
        }
        if(sta != null) {
        	sqlWhere.append(" and sta = ?");
        	args = ArrayUtils.add(args, sta);
        }
        
        
        // 查询记录总数
        
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  order by c_time desc limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<LgsNode> result = jdbcTemplate.query(sqlQueryResult.toString(), args, new BeanPropertyRowMapper<>(LgsNode.class));
        return new Pageable<>(page, getPageSize(), resultTotal, result);
	}
	
	/**
	 * 根据id查询单个节点管理
	 * @param id 节点id
	 * @param companyId 公司id
	 * @return 单个节点
	 * @date 2017-2-8 13:57
	 */
	public LgsNode getLgsNodeById (int id, int companyId) {
		String sql = "select * from lgs_node where id = ? and company_id = ?";
		List<LgsNode> result = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(LgsNode.class), id, companyId);
		if (result.size() > 0) {
			return result.get(0);
		}else{
			return new LgsNode();
		}
	}
	
	/**
	 * 判断节点是否已存在
	 * @param companyId 公司id
	 * @param name 节点名称
	 * @return boolean true存在，false不存在
	 * @date 2017-2-8 14:52
	 */
    public boolean isExistsNode(Integer companyId, String name){
    	String sql = "select * from lgs_node where company_id = ? and name = ? ";
    	List<LgsNode> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LgsNode.class),companyId,name);
    	if(result.size() > 0){
    		return true;
    	}else{
    		return false;
    	}
    }
    
    /**
     * 获取正常状态的节点
     * @param companyId 公司id
     * @param state 状态，1正常，0不正常
     * @return List<LgsNode>
     */
    public List<LgsNode> getNodeList(Integer companyId, int state) {
    	String sql = "select ID 'id',company_id 'companyId', name ,type, sta, c_time 'cTime', c_admin 'cAdmin' from lgs_node where company_id = ? and sta = ?  order by c_time desc";
    	List<LgsNode> result = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(LgsNode.class), companyId,state);
    	return result;
    }


/** ********************  节点 与 erp适用单据表 关联  *******************/


	/**
	 * 获取该公司下为关联的
	 * @param companyId
	 * @return
	 */
	public List<Map<String,Object>> getBiztype(Integer companyId) {
	String sql = "select id,typecode, typename, erpcode from bd_biztype " +
			"where id NOT IN (SELECT re.biztype_id from res_node_biztype re " +
			"join lgs_node n on n.ID=re.node_id " +
			" WHERE n.sta=1 and re.company_id = ?)";
	List<Map<String,Object>> result = jdbcTemplate.queryForList(sql, companyId);
	return result;
}

	/**
	 *添加关联
	 * @param companyId
	 * @param nodeId
	 * @param biztypeId
	 * @return
	 */
	public int insertRes (Integer companyId,Integer nodeId, Integer biztypeId) {
		String sql = "insert into res_node_biztype(company_id, node_id, biztype_id)" +
				"values (:companyId, :nodeId, :biztypeId)";
		HashMap<String, Object> paramMap = new HashMap<>();
		paramMap.put("companyId", companyId);
		paramMap.put("nodeId", nodeId);
		paramMap.put("biztypeId", biztypeId);
		return namedParameterJdbcTemplate.update(sql, paramMap);
	}

	/**
	 * 删除关联关系
	 * @param companyId
	 * @param biztypeId
	 * @return
	 */
	public int deleteRes (Integer companyId,Integer biztypeId) {
		String sql = "delete from res_node_biztype where company_id = ? and  biztype_id =?";
		return jdbcTemplate.update(sql, companyId,biztypeId);
	}

	/**
	 *获取节点关联的 ERP适用单据
	 * @param nodeId
	 * @return
	 */
	public List<Map<String,Object>> getBiztypeByNode(Integer nodeId,Integer companyId) {
		String sql = "SELECT b.id,b.typecode, b.typename, b.erpcode from res_node_biztype re join bd_biztype b" +
				" on b.id=re.biztype_id WHERE re.node_id=? and re.company_id=?";
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sql, nodeId,companyId);
		return result;
	}

}
